package fxification.java;

import com.sun.javafx.functions.Function1;
import com.sun.javafx.runtime.sequence.IntArraySequence;
import com.sun.javafx.runtime.sequence.Sequence;
import fxification.db.DataSet;
import fxification.db.DataSet.Column;
import fxification.db.DataSet.Row;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.Properties;
import javafx.data.Pair;

public class FxSql {
    private static final String USER_KEY = "user";
    private static final String PWD_KEY = "password";

    public static Connection getConnection(
        String driver,
        String url,
        String username,
        String pwd,
        Sequence<Pair> props){

        if(url == null || url.length() == 0){
            throw new RuntimeException("You must provide a valid database URL");
        }

        Connection conn = null;
        java.util.Properties dbProps = getPropsFromPairSequence(props) ;


        try {
            if(driver != null && driver.length() > 0){
                Class.forName(driver);
            }
            
            if(username != null){
                dbProps.put(USER_KEY, username);
                dbProps.put(PWD_KEY, pwd);
            }
            if(dbProps.size() > 0){
                conn = DriverManager.getConnection(url, dbProps);
            }else{
                conn = DriverManager.getConnection(url);
            }

        }catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex);
        }catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return conn;
    }

    public static void queryForDataSet(
            Connection conn,
            String sql,
            Function1<Void, ? super Statement> onStartingQuery,
            Function1<Void, ? super Row> onEachRow,
            Function1<Void, ? super DataSet> onDataSetAvailable,
            Function1<Void, ? super ResultSet> onResultSetAvailable,
            Function1<Void, ? super Throwable> onFailure){

        validateConnection(onFailure, conn);

        Statement stmt;

        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        } catch (SQLException ex) {
            throw Utilities.raiseFailure(onFailure, ex);
        }

        // apply statement updates
        if(onStartingQuery != null){
            onStartingQuery.invoke(stmt);
        }

        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(sql);
            ResultSetMetaData rsInfo = rs.getMetaData();

            if(onResultSetAvailable != null){
                onResultSetAvailable.invoke(rs);
            }
            // only fill DataSet when necessary
            if(onDataSetAvailable != null || onEachRow != null){
                rs.beforeFirst();
                DataSet ds = generateDataSet(rs, rsInfo);
                if(onDataSetAvailable != null){
                    onDataSetAvailable.invoke(ds);
                }
                if(onEachRow != null){
                    for(Iterator<Row> i = ds.getRows().iterator(); i.hasNext();){
                        DataSet.Row r = i.next();
                    }
                }
            }

        } catch (SQLException ex) {
            throw Utilities.raiseFailure(onFailure, ex);
        } catch(Exception ex){
            throw Utilities.raiseFailure(onFailure, ex);
        }

    }

    public static void updateQuery(
            Connection conn,
            String sql,
            Function1<Void, ? super Statement> onStartingQuery,
            Function1<Void, ? super DataSet> onDataSetAvailable,
            Function1<Void, ? super ResultSet> onResultSetAvailable,
            Function1<Void, ? super Integer> onUpdateCompleted,
            Function1<Void, ? super Throwable> onFailure) {
        
        validateConnection(onFailure, conn);
        Statement stmt;

        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        } catch (SQLException ex) {
            throw Utilities.raiseFailure(onFailure, ex);
        }

        // apply statement updates
        if(onStartingQuery != null){
            onStartingQuery.invoke(stmt);
        }

        Integer effectedRows = 0;
        try{
            effectedRows = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

            if(onUpdateCompleted != null){
                onUpdateCompleted.invoke(effectedRows);
            }

            ResultSet rs = stmt.getGeneratedKeys();
            if(onResultSetAvailable != null){
                onResultSetAvailable.invoke(rs);
            }

            if(onDataSetAvailable != null){
                rs.beforeFirst();
                ResultSetMetaData rsInfo = rs.getMetaData();
                DataSet ds = generateDataSet(rs, rsInfo);
                onDataSetAvailable.invoke(ds);
            }
        } catch (SQLException ex) {
            throw Utilities.raiseFailure(onFailure, ex);
        } catch(Exception ex){
            throw Utilities.raiseFailure(onFailure, ex);
        }
    }

    private static Properties getPropsFromPairSequence(Sequence<Pair> props){
        Properties properties = new java.util.Properties();
        if(props != null){
            for(Iterator<Pair> i = props.iterator(); i.hasNext();){
                Pair p = i.next();
                properties.put(p.$name, p.$value);
            }
        }
        return properties;
    }

    private static void validateConnection(Function1<Void,? super Throwable> failure, Connection conn){
        if(conn == null){
            Utilities.raiseFailure(failure, "You must have a valid Connection instance before connecting to database.");
        }
    }

    private static DataSet generateDataSet(ResultSet rs, ResultSetMetaData rsInfo) throws Exception{
        int colCount = rsInfo.getColumnCount();

        DataSet ds = new DataSet();
        ds.setMetaData(rsInfo);
        ds.setColumnCount(colCount);
        ds.setResultSet(rs);

        while(rs.next()){
            Row r = new Row();
            // col info
            for(int i = 1; i <= colCount; i++){
                Object value = rs.getObject(i);
                Column c = new Column();
                c.setValue(value);
                c.setName(rsInfo.getColumnName(i));
                c.setAutoIncrement(rsInfo.isAutoIncrement(i));
                c.setCatalogName(rsInfo.getCatalogName(i));
                c.setScale(rsInfo.getScale(i));
                c.setSchema(rsInfo.getSchemaName(i));
                c.setSize(rsInfo.getPrecision(i));
                c.setTableName(rsInfo.getTableName(i));
                c.setType(rsInfo.getColumnTypeName(i));

                r.add(c);
            }
            ds.addRow(r);
        }

        return ds;
    }
}
